#! /bin/bash
export LANG=zh_CN.UTF-8
PRESTO_HOME=/export/server/presto/bin/presto


${PRESTO_HOME} --catalog hive --server 192.168.88.80:8090 --execute "
--稽核上层数据量
select 1/count(*) from hive.edu_dwd.ydh_dim_class_time;
select 1/count(*) from hive.edu_dwd.ydh_dim_course_upload;
select 1/count(*) from hive.edu_dwd.ydh_fact_student_leave;
select 1/count(*) from hive.edu_dwd.ydh_fact_student_signin;


delete from hive.edu_dwb.ydh_dwb_check_detail_wide where 1=1;
insert into hive.edu_dwb.ydh_dwb_check_detail_wide

-- 班级作息时间表
with ydh_dim_class_time as (
    select
         id
        ,class_id
        ,morning_template_id
        ,morning_begin_time
        ,morning_end_time
        ,afternoon_template_id
        ,afternoon_begin_time
        ,afternoon_end_time
        ,evening_template_id
        ,evening_begin_time
        ,evening_end_time
        ,use_begin_date
        ,use_end_date
    from hive.edu_dwd.ydh_dim_class_time
    group by
         id
        ,class_id
        ,morning_template_id
        ,morning_begin_time
        ,morning_end_time
        ,afternoon_template_id
        ,afternoon_begin_time
        ,afternoon_end_time
        ,evening_template_id
        ,evening_begin_time
        ,evening_end_time
        ,use_begin_date
        ,use_end_date
),
--班级排课表
ydh_dim_course_upload as(
    select
         class_id,class_date,content
    from hive.edu_dwd.ydh_dim_course_upload
    group by class_id,class_date,content
),
--请假表
ydh_fact_student_leave as(
    select
         class_id
        ,student_id
        ,audit_state
        ,leave_type
        ,begin_time
        ,begin_time_type
        ,end_time
        ,end_time_type
        ,days
        ,cancel_state
        ,cancel_time
        ,old_leave_id
        ,valid_state
    from hive.edu_dwd.ydh_fact_student_leave
    group by
         class_id
        ,student_id
        ,audit_state
        ,leave_type
        ,begin_time
        ,begin_time_type
        ,end_time
        ,end_time_type
        ,days
        ,cancel_state
        ,cancel_time
        ,old_leave_id
        ,valid_state
),
--打卡记录表
ydh_fact_student_signin as(
    select
         normal_class_flag
        ,time_table_id
        ,class_id
        ,student_id
        ,signin_time
        ,signin_date
        ,signin_type
    from hive.edu_dwd.ydh_fact_student_signin
    group by
         normal_class_flag
        ,time_table_id
        ,class_id
        ,student_id
        ,signin_time
        ,signin_date
        ,signin_type
),
tmp as (
      select class_id,
             student_id,
             audit_state,
             leave_type,
             begin_time,
             begin_time_type,
             end_time,
             end_time_type,
             days,
             cancel_state,
             cancel_time,
             old_leave_id,
             valid_state
      from ydh_fact_student_leave yfsl
  ),
tmp2 as (
      select class_id,
             student_id,
             normal_class_flag,
             time_table_id,
             signin_time,
             signin_date,
             signin_type
      from  ydh_fact_student_signin yfss
)
select date_format(current_date,'%Y-%m-%d') as create_date,
		class_date,
       t.class_id ,
       t.student_id,
       null as signin_time,             --打卡表
       null as signin_date,             --打卡表
       begin_time,               --请假表
       begin_time_type,
       end_time,
       end_time_type,
       days,
       morning_template_id,     --作息表
       morning_begin_time,
       morning_end_time,
       afternoon_template_id,
       afternoon_begin_time,
       afternoon_end_time,
       evening_template_id,
       evening_begin_time,
       evening_end_time,
       use_begin_date,
       use_end_date             --作息表
from tmp t
left join ydh_dim_class_time  ydct
       on t.class_id=ydct.class_id
inner join ydh_dim_course_upload  ydcu
       on t.class_id=ydcu.class_id
where class_date between  use_begin_date and use_end_date and ydct.class_id is not null
      and ydcu.class_date>=substr(t.begin_time,1,10) and ydcu.class_date <=substr(t.end_time,1,10)
union all
select date_format(current_date,'%Y-%m-%d') as create_date,
	   class_date,
       t1.class_id ,
       t1.student_id,
       signin_time,             --打卡表
       signin_date,             --打卡表
       null as begin_time,               --请假表
       null as begin_time_type,
       null as end_time,
       null as end_time_type,
       null as days,
       morning_template_id,     --作息表
       morning_begin_time,
       morning_end_time,
       afternoon_template_id,
       afternoon_begin_time,
       afternoon_end_time,
       evening_template_id,
       evening_begin_time,
       evening_end_time,
       use_begin_date,
       use_end_date             --作息表
from tmp2 t1
left join ydh_dim_class_time  ydct
       on t1.time_table_id=ydct.id
right join ydh_dim_course_upload  ydcu
       on t1.class_id=ydcu.class_id
      and ydcu.class_date=t1.signin_date
where ydct.class_id is not null
;
"
a=$?
if [ ${a} -eq 0 ];then 
	echo 'Program running status return code:' ${a}
	echo '=============================打完收工！================================='
else 
	echo 'Program running status return code:' ${a}
	echo '=============================程序出BUG咯！=============================='
	fi